Excelのスピルを使って複数の値を返そう

こんにちは、小澤です。

普段は機械学習やAlteryxをやっているのですが、 そんなに使ってるわけではないのに、社内ではなぜか「Excelに詳しい人」という扱いも受けています。

普段、そんなにExcelを使うわけではないので「Excelのブログ記事はもう書かないぞ！」と人生で何度か誓ったわけですが、今回はせっかくなので(何が?)Excelの記事を書きます。

Excelにおける複数の値を返す数式

さて、多くの方が普段あまり意識することは無いでしょうが、Excelで数式を書く際に単一の値ではなく複数の値が結果として返ってくる、ということがあります。

最も簡単な例として以下のような数式を考えてみましょう。

A1:A3 とセル範囲をそのまま指定しているだけなので、3つの値が返ってきます。

従来のExcelだと、このような式の場合入力しているセルの行または列の位置に合わせた結果のみが返ってきます。

スピルを使うと、B1セルやE1セルに同じ数式を入れた際に、A3やG1まで自動的に補完されます。

スピルは利用可能なバージョンのExcelであれば特別な操作をすることなく、 通常通り入力するだけでこのような結果となります。

スピルによって補完された部分はゴーストと呼ばれる形になってります。 数式バーがグレーアウトされており、単体では編集できない状態になっています。

なお、従来のように複数返ってくるけど対応した行や列の結果だけほしいといった場合には「@」を付けることで実現できます。

実用的な使いどころ

ここまでは非常に簡単な数式のみでその動きを見てきました。 そのため、どういう場面で便利かイマイチわからないかと思います。

スピルを使う例として以下のような場面を考えてみましょう。

表の形式でシミュレーションする

ある商品の価格設定と販売個数から、どの程度売れば目標が達成できるか考えてみるとしましょう。

以下のような売上目標に対して、各価格設定では何個売れば目標達成となるかを見ていくとします。

B8セルに以下のような数式を入力します。

行列双方でスピルによって表のすべての値が計算されます。 ついでに条件付き書式で目標に達しているものを色付けすることで、表が完成しました。

この分析に関しては、What-If分析のデータテーブルでも同様のことが可能ですが、 スピルの例としては、比較的わかりやすいものではないかと思われます。

複数の結果を返す関数の利用

Excelを使っていて、関数を利用しないということは通常あまりまないでしょう。 スピルと合わせると複数の結果を返す関数なんてものも活用できます。

その中の一つであるSORT関数を見てみましょう。 以下のようなデータを用意します。

そんな関数の中には、「返ってくる値が複数」というものもあります。

この関数はデータをソートするものとなっています。

• 第1引数の範囲に対して
• 第2引数で何列名をソートするときの基準にするかを指定
• 第3引数で昇順か降順かを指定します

結果は以下のように、元と同じ形式のままソートされたものが返ってきます。

その他にも特定の条件を満たす行のみを取得するFILTER関数や、重複を削除するUNIQUE関数などが用意されています。

また、以前ご紹介したXLOOKUP関数もスピル機能に対応しています。

• 正式リリースされたExcelのXLOOKUP関数の動きを確認してみた | Developers.IO

以下のようなデータを用意します。

このデータを使って以下の表を埋めるとします。

XLOOKUP関数を以下のように使うと商品と価格は連続してるのでそのままスピルで補完されます。

また、以下のようにすべてのIDに対して一度の商品名を取得することも可能です。

ただし、行列両方には対応してくれなかったのでその点はご注意ください。

また、後者のように行方向に補完した場合、小計列の計算は以下のようにスピルを使った補完が可能です。

スピルで補完されたデータの先頭行のみの指定と「#」と入れると自動的に同じ範囲で補完してくれます。

おまけ : スピルと配列数式

Excelでは以前から、似たようなことを実現する機能として、配列数式と言うものがありました。

最初の =A1:A3 の例でいうと、行方向に3つのセルを洗濯した状態でこの数式を入れて、Ctrl-Shist-Enterで確定させることで、式が「{}」でくくられて3つのセルがワンセットとなります。

スピルは、これとほぼ同等の機能を提供するものであるとともに、対象とする範囲をあらかじめ指定しなくても自動的に決めてくれため、「動的配列数」とも呼ばれるようです。

おわりに

今回はExcelのスピルを紹介させていただきました。 最新の機能！というわけではないですが、比較的新しい機能であるため、便利な使い道をぜひどんどん探ってみてください。